from time import sleep
import traceback
from flask import Blueprint,jsonify,request
from sqlalchemy import func, and_
import  models
import json
from werkzeug.serving import WSGIRequestHandler
WSGIRequestHandler.protocol_version = "HTTP/1.1"
import os
import config
from config import debug, log as log
from config import makeNewSession as makeNewSession
from API.messageAPI import get_group_member_list
from openpyxl import load_workbook
from werkzeug.datastructures import FileStorage
from random import randint

students = Blueprint('students',__name__)
# 获取学生数据 指定分页
@students.route("/studentData")
def studentData():
    curPage = request.args.get("page",1,type=int)
    per_page = request.args.get("limit",15,type=int)
    getData = json.loads(request.args.get("searchParams","{}"))
    print(getData, bool(getData),"text",getData)
    session = makeNewSession()
    if getData:
        condition = and_(
            models.Student.uid.contains(getData.get("uid",0)),
            models.Student.name.contains(getData.get("name",'')),
            models.Student.sex.contains(getData.get("sex",'')),
            models.Student.banji.contains(getData.get("banji",'')),
            models.Student.phone.contains(getData.get("phone",'')),
            models.Student.QQ.contains(getData.get("QQ",'')),
            models.Student.beizhu.contains(getData.get("beizhu",''))
        )

    # 分页
    all_ = session.query(models.Student).offset((curPage-1)*per_page).limit(per_page)   if not getData else session.query(models.Student).filter(condition).offset((curPage-1)*per_page).limit(per_page)  

    # count  = session.query(func.count(all_)).scalar()
    # 获取总条数
    count =session.query(func.count(models.Student.uid)).scalar() if not getData else session.query(func.count(models.Student.uid)).filter(condition).scalar()

    result = {
        "code": 0,
        "msg": "",
        "count": count,
        "data": []
        }
    for each in all_:
        result['data'].append(each.to_json())
    session.close()
    return jsonify(result)

# 添加学生数据
@students.route("/addStudentData",methods=["POST"])
def addStudentData():
    getData = request.get_json()
    if not getData: return "you need post some args!",406
    session = makeNewSession()
    try:
        obj = models.Student(**getData)
        session.add(obj)
        session.commit()
        session.close()
        return "add successfully"
    except:
        print(getData,"插入数据库失败")
        traceback.print_exc()
        session.rollback()
        session.close()
        return ("add failure",500)


@students.route("/addStudentsData",methods=["POST"])
def addStudentsData():
    getDatas = request.get_json()
    session = makeNewSession()
    for data in getDatas:
        try:
            obj = models.Student(**data)
            session.add(obj)
            session.commit()
        except:
            print(data,"插入数据库失败")
            session.rollback()
    
    session.close()
    return "add successfully"

# 删除学生数据
@students.route("/delStudentData",methods=["POST"])
def delStudentData():
    getData = request.get_json()
    if not getData: return "you need post some args!",406
    session = makeNewSession()

    try:
        session.query(models.Student).filter(models.Student.uid==getData['uid']).delete()
        session.commit()
        session.close()
        return "del successfully"
    except:
        session.rollback()
        session.close()
        return "del failure",500

@students.route("/delStudentsData",methods=["POST"])
def delStudentsData():
    getDatas = request.get_json()
    # print(getDatas)
    session = makeNewSession()
    try:
        session.query(models.Student).filter(models.Student.uid.in_(getDatas)).delete()
        session.commit()
        session.close()
        return "del successfully"
    except:
        session.rollback()
        session.close()
        return "del failure",500
# 更新学生数据
@students.route("/updateStudentData",methods=["POST"])
def updateStudentData():
    getData = request.get_json()
    if not getData: return "you need post some args!",406
    print(getData)
    session = makeNewSession()
    try:
        session.query(models.Student).filter(models.Student.uid==getData["uid"]).update(getData)
        session.commit()
        session.close()
        return "update successfully"
    except:
        session.rollback()
        session.close()
        return "update failure",500


def load_sheet(filePath ,sheetName,check_field,title_and_field):
    if isinstance(filePath, str):wb = load_workbook(filePath)
    elif isinstance(filePath, FileStorage): wb = load_workbook(filePath, data_only=True)
    
    # 若存在表名students选择students表,否则选择第一个表
    if sheetName in wb.sheetnames:
        ws = wb[sheetName]
    else:
        ws = wb[wb.sheetnames[0]]

    attrs = {}
    for title in ws[1]:
        for t,f in title_and_field:
            if title.value.strip() == t:
                attrs[title.column]=f
    attrs_values = attrs.values()
    print("attrs",attrs, attrs_values)
    for field in check_field:
        if field not in attrs_values:
            # raise Exception(f"not contain field {field}")
            return dict(code=-1,msg=f"not contain field {field}")

    out_data = []
    error_list = []
    for row_i in range(2, ws.max_row+1):
        each_data = {}
        flag = True
        for col in attrs:
            value = ws.cell(row_i, col).value
            if value is None and attrs[col] in check_field:
                error_list.append(each_data)
                flag = False
                break
            
            each_data[attrs[col]] = value
        if flag: out_data.append(each_data)
    return dict(code=0,data=out_data,error=error_list,msg="ok")



# 从群聊中导入数据
@students.route("/loadDataFromFile",methods=["POST"])
def loadDataFromGroup():
    file = request.files["file"]
    # print(type(file))
    r = load_sheet(file,"students",["uid","name","banji"],(("学号","uid"), ("姓名","name"), ("性别","sex"), ("班级","banji"), ("电话","phone"), ("QQ","QQ"), ("备注","beizhu")))
    print(r)
    return r

# 导入数据_关联QQ
@students.route("/math_QQ",methods=["POST"])
def math_QQ():
    # data = json.loads(request.g)
    data = request.get_json()
    print("data ",data)
    banji_all = set()
    info_map = {}
    session = makeNewSession()
    for each in data:
        info_map[each["name"]] = [each["uid"], None]
        if each["banji"] not in banji_all:
            banji_all.add(int(each["banji"]))
    classes = session.query(models.Classes.mainGroup).filter(models.Classes.classesID.in_(banji_all))
    session.close()
    for cls in classes:
        mems = get_group_member_list(cls)
        for mem in mems:
            name = mem["card"] or mem["nickname"]
            m_info = info_map.get(name)
            if m_info: m_info[1]=mem["user_id"]
        sleep(randint(1,3)*0.1)
    return dict(code=0,msg="ok",data=info_map)

# 导入数据_提交
@students.route("/daoru_submit",methods=["POST"])
def daoru_submit():
    data = request.get_json()
    print(data)
    if not data: return "error",406
    try:
        session = makeNewSession()
        qs = session.query(models.Student.uid).all()
        has_uid = set(map(lambda d: d[0], qs))
        update_fields = ("name", "sex", "banji", "phone", "QQ", "beizhu")

        for each in data:
            each["uid"] = int(each["uid"])

            if each["uid"] in has_uid:
                new_d = {k:each[k] for k in update_fields if each.get(k)}
                session.query(models.Student).filter(models.Student.uid == each["uid"]).update(new_d)
            else:
                session.add(models.Student(uid=int(each["uid"]), name=each["name"], sex=each.get("sex"), banji=int(each["banji"]), phone=each.get("phone", ''), QQ=str(each["QQ"]), beizhu=each.get("beizhu")))
        session.commit()
        session.close()
        return {"code":0, "msg":"ok", "count": len(data)},200
    except:
        traceback.print_exc()
        session.rollback()
        session.close()
        return 'error',500
    
